package net.bwie;

import org.apache.flink.streaming.api.environment.StreamExecutionEnvironment;
import org.apache.flink.table.api.TableResult;
import org.apache.flink.table.api.bridge.java.StreamTableEnvironment;

public class DwsEntranceBehavior {
    public static void main(String[] args) throws Exception {

        // 创建执行环境
        StreamExecutionEnvironment evn = StreamExecutionEnvironment.getExecutionEnvironment();
        evn.setParallelism(1);

        //  创建表环境
        StreamTableEnvironment tenv = StreamTableEnvironment.create(evn);


        // 汇总数据


        // 窗口计算
        tenv.executeSql("CREATE TABLE dws_summary_entrance_guard_log (\n" +
                // 窗口开始时间
                "window_start STRING,\n" +
                // 窗口结束时间
                "window_end STRING,\n" +

                // 地区名称
                "districtName STRING,\n" +

                // 刷卡次数
                "card_cnt INT,\n" +
                // 刷APP次数
                "pw_cnt INT,\n" +
                // 人脸识别次数
                "face_cnt INT,\n" +
                // 远程次数
                "remote_cnt INT,\n" +

                ") WITH (\n" +
                " 'connector' = 'doris',\n" +
                "'fenodes' = 'node102:9030',\n" +
                "'table.identifier' = 'dws_sca.dws_summary_entrance_guard_log',\n" +
                "'username' = 'root',\n" +
                "'password' = '123456',\n" +
                "'sink.label-prefix' = 'doris_entrance_guard_log'\n"
        );


        TableResult tableResult = tenv.executeSql("SELECT\n" +
                "window_start,\n"+
                "window_end,\n"+

                "districtName,\n" +

                "sum(if(openMode = '刷卡' ,1,0)) as card_cnt,\n" +
                "sum(if(openMode = '刷APP' ,1,0)) as pw_cnt,\n" +
                "sum(if(openMode = '人脸识别' ,1,0)) as face_cnt,\n" +
                "sum(if(openMode = '远程' ,1,0)) as remote_cnt\n" +
                "FROM dwd_summary_cars_log  \n" +
                "GROUP BY window_start,window_end,districtName;\n"
        );
        tableResult.print();

//     tenv.executeSql("INSERT INTO dws_summary_cars_log" +
//                "SELECT\n" +
//                "window_start,\n"+
//                "window_end,\n"+
//
//                "districtName,\n" +
//
//                "sum(if(openMode = '刷卡' ,1,0)) as card_cnt,\n" +
//                "sum(if(openMode = '刷APP' ,1,0)) as pw_cnt,\n" +
//                "sum(if(openMode = '人脸识别' ,1,0)) as face_cnt,\n" +
//                "sum(if(openMode = '远程' ,1,0)) as remote_cnt\n" +
//                "FROM dwd_summary_cars_log  \n" +
//                "GROUP BY window_start,window_end,districtName;\n"
//        );


        //  触发执行
        evn.execute("DwsEntranceBehavior");
    }
}
